﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using TurmaLib.Basic;


namespace TurmaLib.Repositorio
{
    public class RepositorioTurma
    {
        private SqlConnection conexaoBanco = null;
        private string conexaoSql = @"Data Source=VITOR-PC\SQLEXPRESS;Initial Catalog=Escola; user id=sa; pwd=jva;";
        private static RepositorioTurma instanciaBanco;

        private RepositorioTurma()
        {
        }

        public static RepositorioTurma getInstancia()
        {
            if (instanciaBanco == null)
            {
                instanciaBanco = new RepositorioTurma();
            }
            return instanciaBanco;
        }

        public void incluirTurma(Turma novaTurma)
        {
            Funcionario funcionario = new Funcionario();
            Int32 cont = 0;

            try
            {
                conexaoBanco = new SqlConnection(conexaoSql);

                conexaoBanco.Open();

                SqlCommand insert = new SqlCommand("exec SP_insertTurma @descricao, @turno, @qtdAlunosMax, @CodSerie", conexaoBanco);

                insert.Parameters.AddWithValue("@descricao", novaTurma.DescTurma);
                insert.Parameters.AddWithValue("@turno", novaTurma.TurnoTurma);
                insert.Parameters.AddWithValue("@qtdAlunosMax", novaTurma.QtdAlunosMax);
                insert.Parameters.AddWithValue("@CodSerie", novaTurma.Serie.CodSerie);

                insert.ExecuteNonQuery();

                while (cont < novaTurma.Funcionarios.Count)
                {
                    funcionario = novaTurma.Funcionarios[cont];
                    SqlCommand insertprofTurma = new SqlCommand("exec SP_insertProf_Turma_Serie @MatFunc, @CodSerie", conexaoBanco);
                    insertprofTurma.Parameters.AddWithValue("@MatFunc", funcionario.MatFunc);
                    insertprofTurma.Parameters.AddWithValue("@CodSerie", novaTurma.Serie.CodSerie);
                    insertprofTurma.ExecuteNonQuery();
                    cont++;
                }

            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException();
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException();
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }

        public List<Turma> pesquisarTurmas()
        {
            List<Turma> turmas = new List<Turma>();
            Turma turma = null;

            try
            {
                conexaoBanco = new SqlConnection(conexaoSql);

                SqlCommand select = new SqlCommand("select Turma.DescTurma, Serie_Turma.CodSerie from Turma"+
                                                    "inner join Serie_Turma on Turma.CodTurma = Serie_Turma.CodSerie", conexaoBanco);                            

                SqlDataReader consulta;

                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    turma = new Turma();
                    turma.DescTurma = consulta["DescTurma"].ToString();
                    turma.Serie.CodSerie = Convert.ToInt32(consulta["CodSerie"]);
                    turmas.Add(turma);
                }

                return turmas;
            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException("Erro de acesso ao banco!!!");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException("Erro de comando sql!!!");
            }
            finally
            {
                conexaoBanco.Dispose();
            }

        }

        /*
        public List<Turma> listarTurmas()
        {
            List<Turma> listaTurmas = new List<Turma>();
            Turma turma = null;

            try
            {
                conexaoBanco = new SqlConnection(conexaoSql);

                SqlCommand select = new SqlCommand("select * from Turma", conexaoBanco);

                SqlDataReader consulta;

                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    turma = new Turma();
                    turma.CodTurma = Convert.ToInt32(consulta["CodTurma"]);
                    turma.DescTurma = consulta["DescTurma"].ToString();
                    turma.TurnoTurma = consulta["Turno"].ToString();
                    turma.QtdAlunosMax = Convert.ToInt32(consulta["QtdAlunosMax"]);
                    listaTurmas.Add(turma);
                }

                return listaTurmas;
            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException("Erro de acesso ao banco!!!");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException("Erro de comando sql!!!");
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }

        public List<SerieTurma> listarSerieTurmas()
        {
            List<SerieTurma> listaSeriesTurmas = new List<SerieTurma>();
            SerieTurma seriesTurmas = null;

            try
            {
                conexaoBanco = new SqlConnection(conexaoSql);

                SqlCommand select = new SqlCommand("select * from Serie_Turma", conexaoBanco);

                SqlDataReader consulta;

                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    seriesTurmas = new SerieTurma();
                    seriesTurmas.QtdAlunos = Convert.ToInt32(consulta["QtdAlunos"]);
                    seriesTurmas.CodSerie = Convert.ToInt32(consulta["CodSerie"]);
                    seriesTurmas.CodTurma = Convert.ToInt32(consulta["CodTurma"]);
                    listaSeriesTurmas.Add(seriesTurmas);
                }

                return listaSeriesTurmas;
            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException("Erro de acesso ao banco!!!");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException("Erro de comando sql!!!");
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }

        public List<ProfessorTurma> listarProfessores()
        {
            List<ProfessorTurma> listaProfessores = new List<ProfessorTurma>();
            ProfessorTurma professor = null;

            try
            {
                conexaoBanco = new SqlConnection(conexaoSql);

                SqlCommand select = new SqlCommand("select * from Professor_Turma", conexaoBanco);

                SqlDataReader consulta;

                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    professor = new ProfessorTurma();
                    professor.MatFunc = Convert.ToInt32(consulta["MatFunc"]);
                    professor.CodTurma = Convert.ToInt32(consulta["CodTurma"]);
                    listaProfessores.Add(professor);
                }

                return listaProfessores;
            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException("Erro de acesso ao banco!!!");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException("Erro de comando sql!!!");
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }
        */

        public List<Funcionario> pesquisarProfessores(Turma obj)
        {
            List<Funcionario> listaFuncionarios = new List<Funcionario>();
            Funcionario funcionario;
            Disciplina disciplina;
            SqlDataReader consulta;

            conexaoBanco = new SqlConnection(conexaoSql);

            SqlCommand select = new SqlCommand("select distinct f.MatFunc, f.NomeFunc, d.NomeDisc from Funcionario as f inner join Professor_Disciplina" 
                +" as pd on pd.MatFunc = f.MatFunc inner join Disciplina as d on d.CodDisc = pd.CodDisc inner join Professor_Serie as ps on"
                + " ps.MatFunc = f.MatFunc inner join Professor_Turma as pt on pt.MatFunc = f.MatFunc inner join Serie as s on s.CodSerie = ps.CodSerie" 
                +" where DescSerie = @DescSerie and CodTurma = @CodTurma", conexaoBanco);

            select.Parameters.AddWithValue("@DescSerie", obj.Serie.DescSerie);
            select.Parameters.AddWithValue("@CodTurma", obj.CodTurma);

            try
            {
                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    funcionario = new Funcionario();
                    funcionario.MatFunc = Convert.ToInt32(consulta["MatFunc"]);
                    funcionario.NomeFunc = consulta["NomeFunc"].ToString();

                    disciplina = new Disciplina();
                    disciplina.Nome = consulta["NomeDisc"].ToString();

                    funcionario.Disciplina = disciplina;
                    
                    listaFuncionarios.Add(funcionario);
                }

                return listaFuncionarios;

            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException("Erro de acesso ao banco!!!");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException("Erro de comando sql!!!");
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }
        

        public List<Serie> listarSeries()
        {
            SqlDataReader consulta;
            Serie serie;
            List<Serie> listaSeries = new List<Serie>();

            conexaoBanco = new SqlConnection(conexaoSql);

            SqlCommand select = new SqlCommand("select * from Serie", conexaoBanco);

            try
            {
                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    serie = new Serie();
                    serie.CodSerie = Convert.ToInt32(consulta["CodSerie"]);
                    serie.DescSerie = consulta["DescSerie"].ToString();
                    listaSeries.Add(serie);
                }
                return listaSeries;
            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException("Erro de acesso ao banco!!!");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException("Erro de comando sql!!!");
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }
        
        public List<Funcionario> listarFuncinarios()
        {
            List<Funcionario> listaFuncionarios = new List<Funcionario>();
            Funcionario funcionario;
            SqlDataReader consulta;

            conexaoBanco = new SqlConnection(conexaoSql);

            SqlCommand select = new SqlCommand("select f.MatFunc, f.NomeFunc, d.NomeDisc from Funcionario as f inner join Professor_Disciplina as pd on" 
                +" f.MatFunc = pd.MatFunc inner join Disciplina as d on d.CodDisc = pd.CodDisc where Função = 'Professor'", conexaoBanco);

            try
            {
                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    funcionario = new Funcionario();
                    funcionario.MatFunc = Convert.ToInt32(consulta["MatFunc"]);
                    funcionario.NomeFunc = consulta["NomeFunc"].ToString();

                    Disciplina disciplina = new Disciplina();
                    disciplina.Nome = consulta["NomeDisc"].ToString();

                    funcionario.Disciplina = disciplina;

                    listaFuncionarios.Add(funcionario);
                }

                return listaFuncionarios;

            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException("Erro de acesso ao banco!!!");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException("Erro de comando sql!!!");
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }
        

        public void alterar(Turma antiga, Turma nova)
        {
            conexaoBanco = new SqlConnection(conexaoSql);
            int cont = 0;
            Funcionario funcionario;
            try
            {
                conexaoBanco.Open();

                for (int i = 0; i < antiga.Funcionarios.Count; i++)
                {
                    funcionario = antiga.Funcionarios[i];
                    SqlCommand alterarTurma = new SqlCommand("exec SP_AlterarTurma @MatFunc, @CodTurma, @DescSerie", conexaoBanco);
                    alterarTurma.Parameters.AddWithValue("@MatFunc", funcionario.MatFunc);
                    alterarTurma.Parameters.AddWithValue("@CodTurma", antiga.CodTurma);
                    alterarTurma.Parameters.AddWithValue("@DescSerie", antiga.Serie.DescSerie);
                    alterarTurma.ExecuteNonQuery();
                }


                for (int i = 0; i < nova.Funcionarios.Count; i++)
                {
                    funcionario = nova.Funcionarios[i];
                    SqlCommand alterarTurma2 = new SqlCommand("exec SP_AlterarTurma2 @MatFunc, @CodTurma, @DescSerie", conexaoBanco);
                    alterarTurma2.Parameters.AddWithValue("@MatFunc", funcionario.MatFunc);
                    alterarTurma2.Parameters.AddWithValue("@CodTurma", nova.CodTurma);
                    alterarTurma2.Parameters.AddWithValue("@DescSerie", nova.Serie.DescSerie);
                    alterarTurma2.ExecuteNonQuery();
                }

            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException();
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException();
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }


        public List<Turma> pesquisarTurma()
        {
            List<Turma> turmas = new List<Turma>();
            Turma turma = null;

            try
            {
                conexaoBanco = new SqlConnection(conexaoSql);

                SqlCommand select = new SqlCommand("select Serie.CodSerie, Turma.DescTurma from Serie " +
                                                   "inner join Serie_Turma on Serie.CodSerie = Serie_Turma.CodSerie " +
                                                   "inner join Turma on Turma.CodTurma = Serie_Turma.CodTurma " +
                                                   "where Turma.CodTurma in (select CodTurma from Serie_Turma) " +
                                                   "and Serie.CodSerie in (select CodSerie from Serie_Turma)", conexaoBanco);

                SqlDataReader consulta;

                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                while (consulta.Read())
                {
                    turma = new Turma();
                    turma.Serie.CodSerie = Convert.ToInt32(consulta["CodSerie"]);
                    turma.DescTurma = consulta["DescTurma"].ToString();
                    turmas.Add(turma);
                }

                return turmas;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conexaoBanco.Dispose();
            }
        }

        public void excluir(Turma obj)
        {
            conexaoBanco = new SqlConnection(conexaoSql);

            try
            {

                SqlCommand sql = new SqlCommand("delete from Disciplina where CodDisc=@CodDisc", conexaoBanco);

                sql.Parameters.AddWithValue("@CodDisc", obj.CodTurma);

                conexaoBanco.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Não é possível excluir, existe um Professor ou Turma cadastrado para essa Disciplina!");
            }

            finally
            {
                conexaoBanco.Dispose();
            }
        }

        public List<Turma> listaTotal()
        {
            List<Turma> lista = new List<Turma>();
            Turma turma = new Turma(); ;
            Serie serie;
            List<Funcionario> funcLista;
            Funcionario func;
            SqlDataReader consulta;
            SqlDataReader consulta2;

            conexaoBanco = new SqlConnection(conexaoSql);
            SqlConnection conexaoBanco2 = new SqlConnection(conexaoSql);

            SqlCommand select = new SqlCommand("select distinct s.CodSerie, t.CodTurma, t.DescTurma, s.DescSerie, st.QtdAlunos, t.QtdAlunosMax," 
                +"t.Turno from turma as t inner join serie_turma as st on t.CodTurma = st.CodTurma inner join serie as s on s.CodSerie = st.CodSerie" 
                +" inner join Professor_Serie as ps on ps.CodSerie = st.CodSerie inner join Professor_Turma as pt on pt.MatFunc = ps.MatFunc inner join" 
                +" Funcionario as f on f.MatFunc = pt.MatFunc where f.Função = 'professor' order by descserie, descturma", conexaoBanco);

            SqlCommand select2 = new SqlCommand("select distinct pt.CodTurma, f.NomeFunc, ps.CodSerie, t.DescTurma, s.DescSerie, st.QtdAlunos," 
                +" t.QtdAlunosMax, t.Turno from turma as t inner join serie_turma as st on t.CodTurma = st.CodTurma inner join serie as s" 
                +" on s.CodSerie = st.CodSerie inner join Professor_Serie as ps on ps.CodSerie = st.CodSerie inner join Funcionario as f on" 
                +" f.MatFunc = ps.MatFunc inner join Professor_Turma as pt on pt.MatFunc = f.MatFunc inner join turma on t.CodTurma = pt.CodTurma" 
                +" where f.Função = 'professor' order by descserie, descturma", conexaoBanco2);

            try
            {
                conexaoBanco.Open();

                consulta = select.ExecuteReader(CommandBehavior.CloseConnection);

                
                while (consulta.Read())
                {
                    funcLista = new List<Funcionario>();

                    turma = new Turma();
                    int codTurma = Convert.ToInt32(consulta["CodTurma"]);
                    turma.CodTurma = codTurma;
                    turma.DescTurma = consulta["DescTurma"].ToString();
                    turma.QtdAlunosMax = Convert.ToInt32(consulta["QtdAlunosMax"]);
                    turma.TurnoTurma = consulta["Turno"].ToString();

                    serie = new Serie();
                    int codSerie = Convert.ToInt32(consulta["CodSerie"]);
                    serie.CodSerie = codSerie;
                    serie.DescSerie = consulta["DescSerie"].ToString();

                    conexaoBanco2.Open();
                    consulta2 = select2.ExecuteReader(CommandBehavior.CloseConnection);

                    while (consulta2.Read())
                    {
                        int codSerie2 = Convert.ToInt32(consulta2["CodSerie"]);
                        int codTurma2 = Convert.ToInt32(consulta2["CodTurma"]);

                        if ((codSerie2 == codSerie) && (codTurma2 == codTurma))
                        {
                            func = new Funcionario();
                            func.NomeFunc = consulta2["NomeFunc"].ToString();
                            funcLista.Add(func);
                        }
                    }
                    conexaoBanco2.Close();

                    turma.Serie = serie;
                    turma.Funcionarios = funcLista;
                    lista.Add(turma);
                }

                return lista;

            }
            catch (System.InvalidOperationException)
            {
                throw new ArgumentException();
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw new ArgumentException();
            }
            finally
            {
                conexaoBanco.Dispose();
                conexaoBanco2.Dispose();
            }
        }
        
    }
}
